package cn.gov.pbc.audit.service.impl;

import java.io.File;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.builder.StandardToStringStyle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import cn.gov.pbc.audit.bo.LoanMultiClient;
import cn.gov.pbc.audit.bo.LoanSum;
import cn.gov.pbc.audit.common.InstitutionTypeConst;
import cn.gov.pbc.audit.common.LoanConst;
import cn.gov.pbc.audit.dao.InstitutionMapper;
import cn.gov.pbc.audit.dao.LoanMapper;
import cn.gov.pbc.audit.excel.LoanExcelListener;
import cn.gov.pbc.audit.excel.LoanExcelRow;
import cn.gov.pbc.audit.pojo.Institution;
import cn.gov.pbc.audit.pojo.Loan;
import cn.gov.pbc.audit.service.ILoanService;
import cn.gov.pbc.audit.vo.LoanIndividualItemVo;
import cn.gov.pbc.audit.vo.LoanItemVo;
import cn.gov.pbc.audit.vo.Target105Vo;
import cn.gov.pbc.audit.vo.Target106Vo;
import cn.gov.pbc.audit.vo.Target107Vo;
import cn.gov.pbc.audit.vo.Target108Vo;
import cn.gov.pbc.audit.vo.Target109Vo;
import cn.gov.pbc.audit.vo.Target110Vo;
import cn.gov.pbc.audit.vo.Target1Vo;
import cn.gov.pbc.audit.vo.Target2Vo;
import lombok.extern.slf4j.Slf4j;

@Service("iLoanService")
@Slf4j
public class LoanServiceImpl implements ILoanService {

	private InstitutionTypeConst institutionTypeConst = new InstitutionTypeConst();

	private String filePath = "C:\\project\\audit\\files";

	private String resultFilePath = filePath + File.separator + "result";

	private String target1File = "1-小微企业贷款余额";

	private String target1Filename = resultFilePath + File.separator + target1File + ".xls";

	private String target2File = "2-不良小微企业贷款余额";

	private String target2Filename = resultFilePath + File.separator + target2File + ".xls";

	private String target9File = "9-普惠口径统计错误";

	private String target9Filename = resultFilePath + File.separator + target9File + ".xls";

	private String target100File = "100-数据分析";

	private String target100Filename = resultFilePath + File.separator + target100File + ".xls";

	private String target101File = "101-数据分析";

	private String target101Filename = resultFilePath + File.separator + target101File + ".xls";

	private String target102File = "102-数据分析";

	private String target102Filename = resultFilePath + File.separator + target102File + ".xls";

	private String target103File = "103-数据分析";

	private String target103Filename = resultFilePath + File.separator + target103File + ".xls";

	private String target104File = "104-数据分析";

	private String target104Filename = resultFilePath + File.separator + target104File + ".xls";

	private String target105File = "105-表一：单银行非普惠纳入普惠情况表";

	private String target105Filename = resultFilePath + File.separator + target105File + ".xls";

	private String target106File = "106-表二：全市单银行非普惠纳入普惠口（按企业余额）前20名表";

	private String target106Filename = resultFilePath + File.separator + target106File + ".xls";

	private String target107File = "107-表三：全市普惠口小微企业多头融资大于1000万元区间分布表";

	private String target107Filename = resultFilePath + File.separator + target107File + ".xls";

	private String target108File = "108-表四：全市普惠口小微企业多头融资（按企业余额）前20名情况表";

	private String target108Filename = resultFilePath + File.separator + target108File + ".xls";

	private String target109File = "109-表0：贷款余额和加权平均利率";

	private String target109Filename = resultFilePath + File.separator + target109File + ".xls";

	private String target110File = "110-表0：金融机构贷款方式情况表";

	private String target110Filename = resultFilePath + File.separator + target110File + ".xls";

	@Autowired
	private InstitutionMapper institutionMapper;

	@Autowired
	private LoanMapper loanMapper;

	@Override
	public void validateFiles() {
		File[] files = new File(this.filePath).listFiles();
		System.out.println("-----------------------------------------------------");
		try {
			for (File file : files) {
				if (file.isDirectory()) {
					continue;
				}
				this.translateToList(file);
			}
			System.out.println("*****************************************************");
			System.out.println("数据校验完成");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private List<Loan> translateToList(File file) {
		System.out.println("---------------------------------------------------------------");
		String institutionName = file.getName().substring(0, file.getName().indexOf("."));
		Institution institution = new Institution();
		institution.setName(institutionName);
		int rowCount = this.institutionMapper.insertSelective(institution);
		if (rowCount == 0) {
			throw new RuntimeException("插入institution表错误");
		}
		System.out.println(institutionName);

		LoanExcelListener listerner = new LoanExcelListener();
		listerner.setInstitutionId(institution.getId());
		for (int i = 0; i < 3; i++) {
			listerner.setClientType(i);
			listerner.setRowIndex(2);
			System.out.println("正在读取第" + i + "个sheet");
			EasyExcel.read(file, LoanExcelRow.class, listerner).sheet(i).doRead();
		}
		return listerner.getLoanList();
	}

	@Override
	public void readExcelToDatabase() {

		this.institutionMapper.deleteAll();
		this.loanMapper.deleteAll();

		File[] files = new File(this.filePath).listFiles();
		System.out.println("-----------------------------------------------------");
		try {
			for (File file : files) {
				if (file.isDirectory()) {
					continue;
				}
				this.insertLoan(this.translateToList(file));
			}
			System.out.println("*****************************************************");
			System.out.println("所有excel文件存入数据库");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private void insertLoan(List<Loan> loanList) {
		int rowCount;
		int pageSize = 10;
		int pageCount = loanList.size() / pageSize;
		if (loanList.size() % pageSize != 0) {
			pageCount++;
		}
		for (int i = 0; i < pageCount; i++) {
			int start = i * pageSize;
			int end = start + pageSize;
			if (end > loanList.size()) {
				end = loanList.size();
			}
			List<Loan> subList = loanList.subList(start, end);
			rowCount = this.loanMapper.insertBatch(subList);
			if (rowCount == 0) {
				throw new RuntimeException("插入loan表失败");
			}
		}
		System.out.println("写入数据库完成");
	}

	private void deleteTargetExcel(String filename) {
		File file = new File(this.resultFilePath);
		if (!file.exists()) {
			file.mkdirs();
			file.setWritable(true);
		}
		file = new File(filename);
		if (file.exists()) {
			if (!file.delete()) {
				throw new RuntimeException("删除" + filename + "失败");
			}
		}
	}

	/**
	 * 分析目标1
	 */
	@Override
	public void target1() {
		deleteTargetExcel(this.target1Filename);
		List<Institution> institutionList = this.institutionMapper.selectAll();
		List<Target1Vo> target1VoList = new ArrayList<>(40);
		for (int i = 0; i < institutionList.size(); i++) {
			Target1Vo vo = new Target1Vo();

			Institution institution = institutionList.get(i);
			vo.setName(institution.getName());

			vo.assmbleFrom(i + 1, institution.getName(), this.loanMapper.calculateByInstitutionIdClientType(
					institution.getId(), LoanConst.ClientType.SMALL_FIRM.getCode()));

			target1VoList.add(vo);
		}
		EasyExcel.write(this.target1Filename, Target1Vo.class).sheet(this.target1File).doWrite(target1VoList);
	}

	@Override
	public void target2() {
		this.deleteTargetExcel(this.target2Filename);
		List<Institution> institutionList = this.institutionMapper.selectAll();
		List<Target2Vo> target2VoList = new ArrayList<>(40);
		for (int i = 0; i < institutionList.size(); i++) {
			Target2Vo vo = new Target2Vo();
			target2VoList.add(vo);
			Institution institution = institutionList.get(i);
			BigDecimal loanBalance = this.loanMapper.calculateBadLoanByInstitutionIdClientType(institution.getId(),
					LoanConst.ClientType.SMALL_FIRM.getCode());
			vo.assmbleFrom(i + 1, institution.getName(), loanBalance);
		}

		EasyExcel.write(this.target2Filename, Target2Vo.class).sheet(this.target2File).doWrite(target2VoList);
	}

	@Override
	public void target9() {
		this.deleteTargetExcel(this.target9Filename);
		List<String> firmList = this.loanMapper.selectAllFirm();
		// List<LoanItemVo> lowList = new ArrayList<>(2000);
		List<LoanItemVo> highList = new ArrayList<>(2000);
		System.out.println("共有" + firmList.size() + "家企业需要计算");
		for (int i = 0; i < firmList.size(); i++) {
			if (i % 10 == 0) {
				System.out.println("正在计算第" + (i + 1) + "家企业");
			}
			BigDecimal balance = this.loanMapper.calculateByClientNameClientType(firmList.get(i),
					LoanConst.ClientType.SMALL_FIRM.getCode());
			if (balance.compareTo(new BigDecimal(1000)) < 0) {
				continue;
			}
			this.assmbleLoanItemVoList(highList, firmList.get(i), highList.size() + 1);
		}
		EasyExcel.write(this.target9Filename, LoanItemVo.class).sheet(1, "大于1000").doWrite(highList);
	}

	private void assmbleLoanItemVoList(List<LoanItemVo> voList, String clientName, int index) {
		List<Loan> loanList = this.loanMapper.selectByClientNameClientType(clientName,
				LoanConst.ClientType.SMALL_FIRM.getCode());
		for (Loan loan : loanList) {
			LoanItemVo vo = new LoanItemVo();
			vo.assmbleFromLoad(loan, index, institutionMapper);
			voList.add(vo);
			index++;
		}
	}

	private void assmbleLoanItemVoListSingleInstitution(List<LoanItemVo> voList, String clientName,
			Integer institutionId, int index) {
		List<Loan> loanList = this.loanMapper.selectByClientNameClientTypeInstitutionId(clientName,
				LoanConst.ClientType.SMALL_FIRM.getCode(), institutionId);
		for (Loan loan : loanList) {
			LoanItemVo vo = new LoanItemVo();
			vo.assmbleFromLoad(loan, index, institutionMapper);
			voList.add(vo);
			index++;
		}
	}

	@Override
	public void target100() {
		String[] institutionArr = { "农行温州分行", "龙湾农商行", "温州银行", "温州民商银行", "招商银行温州分行" };
		ExcelWriter excelWriter = EasyExcel.write(this.target100Filename, LoanItemVo.class).build();
		for (int i = 0; i < institutionArr.length; i++) {
			log.info("正在处理{}的数据", institutionArr[i]);
			Institution institution = this.institutionMapper.selectByName(institutionArr[i]);
			if (institution == null) {
				log.info("{}银行无数据", institutionArr[i]);
				continue;
			}
			List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(institution.getId());
			List<LoanItemVo> loanItemVoList = new ArrayList<>();
			log.info("共{}个企业", firmList.size());
			for (int j = 0; j < firmList.size(); j++) {
				if (j % 10 == 0) {
					log.info("正在处理第{}家企业", j);
				}
				BigDecimal balance = this.loanMapper.calculateFirmByClientNameInstitutionId(firmList.get(j),
						institution.getId());
				if (balance.compareTo(new BigDecimal(1000)) < 0) {
					continue;
				}
				this.assmbleLoanItemVoListSingleInstitution(loanItemVoList, firmList.get(j), institution.getId(),
						loanItemVoList.size() + 1);
			}

			WriteSheet writeSheet = EasyExcel.writerSheet(i, institutionArr[i]).build();
			// 第一次写入会创建头
			excelWriter.write(loanItemVoList, writeSheet);

			log.info("{}的数据处理完毕", institutionArr[i]);
		}
		excelWriter.finish();
	}

	@Override
	public void target101() {
		String[] institutionArr = { "农行温州分行", "龙湾农商行", "温州银行", "温州民商银行", "招商银行温州分行" };
		ExcelWriter excelWriter = EasyExcel.write(this.target101Filename, LoanItemVo.class).build();
		for (int i = 0; i < institutionArr.length; i++) {
			log.info("正在处理{}的数据", institutionArr[i]);
			Institution institution = this.institutionMapper.selectByName(institutionArr[i]);
			if (institution == null) {
				log.info("{}银行无数据", institutionArr[i]);
				continue;
			}
			List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(institution.getId());
			List<String> firmMulList = new ArrayList<>();
			log.info("共{}家贷款企业需要计算多头贷款情况", firmList.size());
			for (int k = 0; k < firmList.size(); k++) {
				if (k % 10 == 0) {
					log.info("正在计算第{}家企业多头贷款情况", k);
				}
				String clientName = firmList.get(k);
				int count = this.loanMapper.selectClientCountByClientNameExclueInstitutionId(clientName,
						institution.getId());
				if (count == 0) {
					continue;
				}
				firmMulList.add(clientName);
			}
			List<LoanItemVo> loanItemVoList = new ArrayList<>();
			List<LoanMultiClient> multiClientList = new ArrayList<>();
			log.info("共{}个多头贷款企业", firmMulList.size());
			for (int j = 0; j < firmMulList.size(); j++) {
				if (j % 10 == 0) {
					log.info("正在处理第{}家企业", j);
				}
				BigDecimal balance = this.loanMapper.calculateByClientNameClientType(firmMulList.get(j),
						LoanConst.ClientType.SMALL_FIRM.getCode());
				if (balance.compareTo(new BigDecimal(1000)) < 0) {
					continue;
				}
				LoanMultiClient loanMultiClient = new LoanMultiClient();
				loanMultiClient.setBalance(balance);
				loanMultiClient.setClientName(firmMulList.get(j));
				multiClientList.add(loanMultiClient);
			}
			Collections.sort(multiClientList);
			int endIndex = multiClientList.size() >= 20 ? 20 : multiClientList.size();
			log.info("共有{}家企业涉及多头贷款总余额超1000万元，取前{}", multiClientList.size(), endIndex);
			for (int j = 0; j < endIndex; j++) {
				this.assmbleLoanItemVoList(loanItemVoList, multiClientList.get(j).getClientName(),
						loanItemVoList.size() + 1);
			}

			WriteSheet writeSheet = EasyExcel.writerSheet(i, institutionArr[i]).build();
			// 第一次写入会创建头
			excelWriter.write(loanItemVoList, writeSheet);

			log.info("{}的数据处理完毕", institutionArr[i]);
		}
		excelWriter.finish();
	}

	@Override
	public void target102() {
		String[] institutionArr = { "农行温州分行" };
		ExcelWriter excelWriter = EasyExcel.write(this.target102Filename, LoanIndividualItemVo.class).build();
		for (int i = 0; i < institutionArr.length; i++) {
			log.info("正在处理{}的数据", institutionArr[i]);
			Institution institution = this.institutionMapper.selectByName(institutionArr[i]);
			if (institution == null) {
				log.info("{}银行无数据", institutionArr[i]);
				continue;
			}
			List<String> individualIdList = this.loanMapper.selectIndividualByInstitutionId(institution.getId());
			List<LoanIndividualItemVo> loanIndividualItemVoList = new ArrayList<>();
			log.info("根据个体工商户 客户证件号码共有{}个体工商户需要计算", individualIdList.size());
			for (int j = 0; j < individualIdList.size(); j++) {
				List<Loan> loanList = this.loanMapper.selectIndividualByClientIdInstitutionId(individualIdList.get(j),
						institution.getId());
				if (j % 10 == 0) {
					log.info("正在计算第{}家个体工商户", j);
				}
				int count = loanList.size();
				BigDecimal sum = new BigDecimal("0");
				for (int k = 0; k < loanList.size(); k++) {
					sum = sum.add(loanList.get(k).getLoanBalance());
				}
				for (int k = 0; k < loanList.size(); k++) {
					LoanIndividualItemVo vo = new LoanIndividualItemVo();
					vo.assmbleFromLoan(loanList.get(k), loanIndividualItemVoList.size() + 1, count, sum,
							institutionMapper);
					loanIndividualItemVoList.add(vo);
				}
			}
			Collections.sort(loanIndividualItemVoList);
			WriteSheet writeSheet = EasyExcel.writerSheet(i, institutionArr[i]).build();
			// 第一次写入会创建头
			excelWriter.write(loanIndividualItemVoList, writeSheet);

			log.info("{}的数据处理完毕", institutionArr[i]);
		}
		excelWriter.finish();
	}

	@Override
	public void target103() {

		List<Institution> institutionList = this.institutionMapper.selectAll();
		ExcelWriter excelWriter = EasyExcel.write(this.target103Filename, LoanItemVo.class).build();
		for (int i = 0; i < institutionList.size(); i++) {
			Institution institution = institutionList.get(i);
			log.info("正在处理{}的数据", institution.getName());

			List<Loan> loanList = this.loanMapper.selectFirmByInstitutionId(institution.getId());
			log.info("共有{}条数据需要处理", loanList.size());
			Map<String, List<Loan>> loanMap = new HashMap<>(loanList.size() * 2);
			List<LoanItemVo> loanItemVoList = new ArrayList<>(loanList.size());
			for (Loan loan : loanList) {
				if (loanMap.containsKey(loan.getClientName())) {
					List<Loan> list = loanMap.get(loan.getClientName());
					list.add(loan);
				} else {
					List<Loan> list = new ArrayList<>();
					list.add(loan);
					loanMap.put(loan.getClientName(), list);
				}
				if (loanMap.size() % 10 == 0) {
					log.info("正在往map中添加数据，已添加{}个企业的数据", loanMap.size());
				}
			}
			log.info("map中数据添加完毕，共有{}家企业的数据", loanMap.size());

			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				List<Loan> list = item.getValue();
				BigDecimal sum = new BigDecimal("0");
				for (Loan loan : list) {
					sum = sum.add(loan.getLoanBalance());
				}
				if (sum.compareTo(new BigDecimal("1000")) <= 0) {
					continue;
				}
				for (Loan loan : list) {
					LoanItemVo vo = new LoanItemVo();
					vo.assmbleFromLoad(loan, loanItemVoList.size() + 1, institutionMapper);
					loanItemVoList.add(vo);
					if (loanItemVoList.size() % 10 == 0) {
						log.info("准备好{}条数据将会写入excel", loanItemVoList.size());
					}
				}

			}
			WriteSheet writeSheet = EasyExcel.writerSheet(i, institution.getName()).build();
			// 第一次写入会创建头
			excelWriter.write(loanItemVoList, writeSheet);

			log.info("{}的数据处理完毕", institution.getName());
		}
		excelWriter.finish();
	}

	@Override
	public void target104() {
		ExcelWriter excelWriter = EasyExcel.write(this.target104Filename, LoanItemVo.class).build();
		List<Institution> institutionList = this.institutionMapper.selectAll();
		for (int i = 0; i < institutionList.size(); i++) {
			Institution institution = institutionList.get(i);
			log.info("正在处理{}的数据", institution.getName());

			List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(institution.getId());
			Map<String, List<Loan>> loanMap = new HashMap<>(firmList.size() * 2);

			for (String name : firmList) {
				loanMap.put(name, new ArrayList<Loan>());
			}
			List<Loan> loanList = this.loanMapper.selectByClientType(LoanConst.ClientType.SMALL_FIRM.getCode());
			List<LoanItemVo> loanItemVoList = new ArrayList<>(loanList.size());
			List<LoanSum> loanSumList = new ArrayList<>(loanMap.size());
			for (Loan loan : loanList) {
				if (!loanMap.containsKey(loan.getClientName())) {
					continue;
				}
				loanMap.get(loan.getClientName()).add(loan);
			}
			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				Set<Integer> set = new HashSet<>();
				BigDecimal sum = new BigDecimal("0");
				List<Loan> list = item.getValue();
				if (list.size() == 0) {
					continue;
				}
				for (Loan loan : list) {
					set.add(loan.getInstitutionId());
					sum = sum.add(loan.getLoanBalance());
				}
				if (set.size() < 2) {
					continue;
				}
				if (sum.compareTo(new BigDecimal("1000")) < 0) {
					continue;
				}
				LoanSum loanSum = new LoanSum();
				loanSum.setSum(sum);
				loanSum.setLoanList(list);
				loanSumList.add(loanSum);
			}
			log.info("map处理完毕，准备进行排序");
			Collections.sort(loanSumList);
			int endIndex = loanSumList.size() > 20 ? 20 : loanSumList.size();
			System.out.println(loanSumList.size());
			for (int j = 0; j < endIndex; j++) {
				LoanSum loanSum = loanSumList.get(j);
				for (Loan loan : loanSum.getLoanList()) {
					LoanItemVo vo = new LoanItemVo();
					vo.assmbleFromLoad(loan, loanItemVoList.size() + 1, institutionMapper);
					loanItemVoList.add(vo);
					if (loanItemVoList.size() % 100 == 0) {
						log.info("准备好{}条数据将会写入excel", loanItemVoList.size());
					}
				}
			}

			WriteSheet writeSheet = EasyExcel.writerSheet(i, institution.getName()).build();
			// 第一次写入会创建头
			excelWriter.write(loanItemVoList, writeSheet);

			log.info("{}的数据处理完毕", institution.getName());
		}
		excelWriter.finish();
	}

	@Override
	public void target105() {

		List<Institution> institutionList = this.institutionMapper.selectAll();

		List<Target105Vo> voList = new ArrayList<>(institutionList.size());
		for (int i = 0; i < institutionList.size(); i++) {
			Institution institution = institutionList.get(i);
			log.info("正在处理{}的数据", institution.getName());

			List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(institution.getId());
			Map<String, List<Loan>> loanMap = new HashMap<>(firmList.size() * 2);
			for (String firm : firmList) {
				loanMap.put(firm, new ArrayList<>());
			}
			List<Loan> loanList = this.loanMapper
					.selectByClientTypeInstitutionId(LoanConst.ClientType.SMALL_FIRM.getCode(), institution.getId());
			log.info("共有{}条数据需要处理", loanList.size());

			for (Loan loan : loanList) {
				if (!loanMap.containsKey(loan.getClientName())) {
					continue;
				}
				List<Loan> list = loanMap.get(loan.getClientName());
				list.add(loan);
			}
			log.info("map中数据添加完毕，共有{}家企业的数据", loanMap.size());

			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				List<Loan> list = item.getValue();
				BigDecimal sum = new BigDecimal("0");
				for (Loan loan : list) {
					sum = sum.add(loan.getLoanBalance());
				}
				if (sum.compareTo(new BigDecimal("1000")) <= 0) {
					list.clear();
					continue;
				}
			}
			Target105Vo vo = new Target105Vo();
			vo.setBank(institution.getName());
			Integer firmCount = 0;
			BigDecimal loan = new BigDecimal("0");
			BigDecimal loanBalance = new BigDecimal("0");
			Integer loanCount = 0;
			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				List<Loan> list = item.getValue();
				if (list.size() == 0) {
					continue;
				}
				firmCount++;
				for (Loan loanItem : list) {
					loanCount++;
					loan = loan.add(loanItem.getLoan());
					loanBalance = loanBalance.add(loanItem.getLoanBalance());
				}
			}
			vo.setFirmCount(firmCount);
			vo.setLoan(loan);
			vo.setLoanBalance(loanBalance);
			vo.setLoanCount(loanCount);
			voList.add(vo);
			log.info("{}的数据处理完毕", institution.getName());
		}
		EasyExcel.write(this.target105Filename, Target105Vo.class).sheet(this.target105File).doWrite(voList);
	}

	@Override
	public void target106() {

		List<Institution> institutionList = this.institutionMapper.selectAll();

		List<Target106Vo> resultList = new ArrayList<>(20);
		List<Target106Vo> voList = new ArrayList<>(50000);
		for (int i = 0; i < institutionList.size(); i++) {
			Institution institution = institutionList.get(i);
			log.info("正在处理{}的数据", institution.getName());

			List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(institution.getId());
			Map<String, List<Loan>> loanMap = new HashMap<>(firmList.size() * 2);
			for (String firm : firmList) {
				loanMap.put(firm, new ArrayList<>());
			}
			List<Loan> loanList = this.loanMapper
					.selectByClientTypeInstitutionId(LoanConst.ClientType.SMALL_FIRM.getCode(), institution.getId());
			log.info("共有{}条数据需要处理", loanList.size());

			for (Loan loan : loanList) {
				if (!loanMap.containsKey(loan.getClientName())) {
					continue;
				}
				List<Loan> list = loanMap.get(loan.getClientName());
				list.add(loan);
			}
			log.info("map中数据添加完毕，共有{}家企业的数据", loanMap.size());

			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				List<Loan> list = item.getValue();
				BigDecimal sum = new BigDecimal("0");
				for (Loan loan : list) {
					sum = sum.add(loan.getLoanBalance());
				}
				if (sum.compareTo(new BigDecimal("1000")) <= 0) {
					list.clear();
					continue;
				}
			}
			for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
				List<Loan> list = item.getValue();
				if (list.size() == 0) {
					continue;
				}
				BigDecimal loan = new BigDecimal("0");
				BigDecimal loanBalance = new BigDecimal("0");
				Integer loanCount = 0;
				for (Loan loanItem : list) {
					loanCount++;
					loan = loan.add(loanItem.getLoan());
					loanBalance = loanBalance.add(loanItem.getLoanBalance());
				}
				Target106Vo vo = new Target106Vo();
				vo.setBank(institution.getName());
				vo.setClientName(item.getKey());
				vo.setLoan(loan);
				vo.setLoanBalance(loanBalance);
				vo.setLoanCount(loanCount);
				voList.add(vo);
			}
			log.info("{}的数据处理完毕", institution.getName());
		}
		Collections.sort(voList, new Comparator<Target106Vo>() {
			@Override
			public int compare(Target106Vo o1, Target106Vo o2) {
				return -o1.getLoanBalance().compareTo(o2.getLoanBalance());
			}
		});
		int endIndex = voList.size() > 20 ? 20 : voList.size();
		for (int i = 0; i < endIndex; i++) {
			Target106Vo vo = voList.get(i);
			vo.setIndex(i + 1);
			resultList.add(vo);
		}
		EasyExcel.write(this.target106Filename, Target106Vo.class).sheet(this.target106File).doWrite(resultList);
	}

	@Override
	public void target107() {
		List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(null);
		Map<String, List<Loan>> loanMap = new HashMap<>(firmList.size() * 2);
		List<Target107Vo> voList = new ArrayList<>();
		voList.add(new Target107Vo("1000-3000万（含）", 0, 0, new BigDecimal("0")));
		voList.add(new Target107Vo("3000-6000万（含）", 0, 0, new BigDecimal("0")));
		voList.add(new Target107Vo("6000-1亿元（含）", 0, 0, new BigDecimal("0")));
		voList.add(new Target107Vo("1亿元以上", 0, 0, new BigDecimal("0")));
		for (String name : firmList) {
			loanMap.put(name, new ArrayList<Loan>());
		}
		List<Loan> loanList = this.loanMapper.selectByClientType(LoanConst.ClientType.SMALL_FIRM.getCode());
		for (Loan loan : loanList) {
			if (!loanMap.containsKey(loan.getClientName())) {
				continue;
			}
			loanMap.get(loan.getClientName()).add(loan);
		}
		BigDecimal a = new BigDecimal("1000");
		BigDecimal b = new BigDecimal("3000");
		BigDecimal c = new BigDecimal("6000");
		BigDecimal d = new BigDecimal("10000");
		for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
			Set<Integer> set = new HashSet<>();
			BigDecimal sum = new BigDecimal("0");
			List<Loan> list = item.getValue();
			if (list.size() == 0) {
				continue;
			}
			Target107Vo vo = null;
			for (Loan loan : list) {
				set.add(loan.getInstitutionId());
				sum = sum.add(loan.getLoanBalance());
			}
			if (set.size() < 2) {
				continue;
			}
			if (sum.compareTo(a) <= 0) {
				continue;
			} else if (sum.compareTo(a) > 0 && sum.compareTo(b) <= 0) {
				vo = voList.get(0);
			} else if (sum.compareTo(b) > 0 && sum.compareTo(c) <= 0) {
				vo = voList.get(1);
			} else if (sum.compareTo(c) > 0 && sum.compareTo(d) <= 0) {
				vo = voList.get(2);
			} else {
				vo = voList.get(3);
			}
			Integer firmCount = vo.getFirmCount();
			Integer loanCount = vo.getLoanCount();
			BigDecimal loanBalance = vo.getLoanBalance();
			firmCount++;
			loanCount += list.size();
			loanBalance = loanBalance.add(sum);
			vo.setFirmCount(firmCount);
			vo.setLoanCount(loanCount);
			vo.setLoanBalance(loanBalance);
		}
		EasyExcel.write(this.target107Filename, Target107Vo.class).sheet(this.target107File).doWrite(voList);
	}

	@Override
	public void target108() {
		List<String> firmList = this.loanMapper.selectInclusiveFirmByInstitutionId(null);
		Map<String, List<Loan>> loanMap = new HashMap<>(firmList.size() * 2);
		List<Target108Vo> voList = new ArrayList<>(firmList.size());
		List<Target108Vo> resultList = new ArrayList<>(20);
		for (String name : firmList) {
			loanMap.put(name, new ArrayList<Loan>());
		}
		List<Loan> loanList = this.loanMapper.selectByClientType(LoanConst.ClientType.SMALL_FIRM.getCode());
		for (Loan loan : loanList) {
			if (!loanMap.containsKey(loan.getClientName())) {
				continue;
			}
			loanMap.get(loan.getClientName()).add(loan);
		}
		BigDecimal a = new BigDecimal("1000");
		for (Map.Entry<String, List<Loan>> item : loanMap.entrySet()) {
			Set<Integer> set = new HashSet<>();
			BigDecimal loanSum = new BigDecimal("0");
			BigDecimal loanBalanceSum = new BigDecimal("0");
			List<Loan> list = item.getValue();
			if (list.size() == 0) {
				continue;
			}
			for (Loan loan : list) {
				set.add(loan.getInstitutionId());
				loanSum = loanSum.add(loan.getLoan());
				loanBalanceSum = loanBalanceSum.add(loan.getLoanBalance());
			}
			if (set.size() < 2) {
				continue;
			}
			if (loanBalanceSum.compareTo(a) <= 0) {
				continue;
			}
			Target108Vo vo = new Target108Vo();
			vo.setClientName(item.getKey());
			vo.setLoan(loanSum);
			vo.setLoanBalance(loanBalanceSum);
			vo.setBankCount(set.size());
			vo.setLoanCount(list.size());
			voList.add(vo);
		}
		Collections.sort(voList, new Comparator<Target108Vo>() {

			@Override
			public int compare(Target108Vo o1, Target108Vo o2) {
				return -o1.getLoanBalance().compareTo(o2.getLoanBalance());
			}
		});
		int endIndex = voList.size() > 20 ? 20 : voList.size();
		for (int i = 0; i < endIndex; i++) {
			Target108Vo vo = voList.get(i);
			vo.setIndex(i + 1);
			resultList.add(vo);
		}
		EasyExcel.write(this.target108Filename, Target108Vo.class).sheet(this.target108File).doWrite(resultList);
	}

	@Override
	public void target109() {
		List<String> typeList = this.institutionTypeConst.getTypeList();
		List<Target109Vo> voList = new ArrayList<>();

		ExcelWriter excelWriter = EasyExcel.write(this.target109Filename, Target109Vo.class).build();
		// sheet1,小微企业
		for (int i = 0; i < typeList.size(); i++) {
			String type = typeList.get(i);
			List<String> bankList = this.institutionTypeConst.getByType(type);
			List<Integer> institutionIdList = this.institutionMapper.selectIdByNameList(bankList);
			List<Loan> loanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
					LoanConst.ClientType.SMALL_FIRM.getCode(), institutionIdList, false);
			voList.add(this.assmbleTarget109Vo(loanList, type));
		}
		List<Loan> totalLoanList = this.loanMapper
				.selectByClientTypeInstitutionRangeInclusive(LoanConst.ClientType.SMALL_FIRM.getCode(), null, false);
		voList.add(this.assmbleTarget109Vo(totalLoanList, "全市合计"));
		WriteSheet writeSheet = EasyExcel.writerSheet(0, "小微企业").build();
		excelWriter.write(voList, writeSheet);
		log.info("小微企业写入excel完毕");

		// sheet2, 标注 普惠的 小微企业
		voList.clear();
		for (int i = 0; i < typeList.size(); i++) {
			String type = typeList.get(i);
			List<String> bankList = this.institutionTypeConst.getByType(type);
			List<Integer> institutionIdList = this.institutionMapper.selectIdByNameList(bankList);
			List<Loan> loanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
					LoanConst.ClientType.SMALL_FIRM.getCode(), institutionIdList, true);
			voList.add(this.assmbleTarget109Vo(loanList, type));
		}
		totalLoanList = this.loanMapper
				.selectByClientTypeInstitutionRangeInclusive(LoanConst.ClientType.SMALL_FIRM.getCode(), null, true);
		voList.add(this.assmbleTarget109Vo(totalLoanList, "全市合计"));
		writeSheet = EasyExcel.writerSheet(1, "小微企业普惠").build();
		excelWriter.write(voList, writeSheet);
		log.info("小微企业普惠写入excel完毕");
		// sheet3 小微企业主
		voList.clear();
		for (int i = 0; i < typeList.size(); i++) {
			String type = typeList.get(i);
			List<String> bankList = this.institutionTypeConst.getByType(type);
			List<Integer> institutionIdList = this.institutionMapper.selectIdByNameList(bankList);
			List<Loan> loanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
					LoanConst.ClientType.SMALL_FIRM_OWNER.getCode(), institutionIdList, false);
			voList.add(this.assmbleTarget109Vo(loanList, type));
		}
		totalLoanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
				LoanConst.ClientType.SMALL_FIRM_OWNER.getCode(), null, false);
		voList.add(this.assmbleTarget109Vo(totalLoanList, "全市合计"));
		writeSheet = EasyExcel.writerSheet(2, "小微企业主").build();
		excelWriter.write(voList, writeSheet);
		log.info("小微企业主写入excel完毕");
		// sheet4 个体工商户
		voList.clear();
		for (int i = 0; i < typeList.size(); i++) {
			String type = typeList.get(i);
			List<String> bankList = this.institutionTypeConst.getByType(type);
			List<Integer> institutionIdList = this.institutionMapper.selectIdByNameList(bankList);
			List<Loan> loanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
					LoanConst.ClientType.INDIVIDUAL.getCode(), institutionIdList, false);
			voList.add(this.assmbleTarget109Vo(loanList, type));
		}
		totalLoanList = this.loanMapper.selectByClientTypeInstitutionRangeInclusive(
				LoanConst.ClientType.INDIVIDUAL.getCode(), null, false);
		voList.add(this.assmbleTarget109Vo(totalLoanList, "全市合计"));
		writeSheet = EasyExcel.writerSheet(3, "个体工商户").build();
		excelWriter.write(voList, writeSheet);
		log.info("个体工商户写入excel完毕");
		excelWriter.finish();
	}

	private Target109Vo assmbleTarget109Vo(List<Loan> loanList, String institutionType) {
		Target109Vo vo = new Target109Vo();
		vo.setInstitutionType(institutionType);
		BigDecimal loan = new BigDecimal("0");
		BigDecimal loanBalance = new BigDecimal("0");
		BigDecimal rate = new BigDecimal("0");
		BigDecimal zero = new BigDecimal("0");

		for (Loan loanItem : loanList) {
			if (loanItem.getLoanBalance().compareTo(new BigDecimal("0")) <= 0) {
				continue;
			}
			loanBalance = loanBalance.add(loanItem.getLoanBalance());
			loan = loan.add(loanItem.getLoan());
			rate = rate.add(loanItem.getLoan().multiply(loanItem.getInterestRate()));
		}
		vo.setLoanBalance(loanBalance.setScale(2, RoundingMode.HALF_UP));
		if (loan.compareTo(zero) == 0) {
			vo.setRate(rate);
		} else {
			vo.setRate(rate.divide(loan, RoundingMode.HALF_UP).setScale(2, RoundingMode.HALF_UP));
		}

		return vo;
	}

	@Override
	public void target110() {

		ExcelWriter excelWriter = EasyExcel.write(this.target110Filename, Target110Vo.class).build();
		List<Target110Vo> voList = new ArrayList<>();
		List<Loan> loanList = this.loanMapper.selectByClientTypeInclusive(LoanConst.ClientType.SMALL_FIRM.getCode(), false);
		
		this.calTarget110(loanList, "小微企业贷款", voList);
		
		loanList = this.loanMapper.selectByClientTypeInclusive(LoanConst.ClientType.SMALL_FIRM.getCode(), true);
		this.calTarget110(loanList, "小微企业普惠口径", voList);
		
		loanList = this.loanMapper.selectByClientTypeInclusive(LoanConst.ClientType.SMALL_FIRM_OWNER.getCode(), false);
		this.calTarget110(loanList, "小微企业主贷款", voList);
		
		loanList = this.loanMapper.selectByClientTypeInclusive(LoanConst.ClientType.INDIVIDUAL.getCode(), false);
		this.calTarget110(loanList, "个体工商户贷款", voList);
		
		WriteSheet writeSheet = EasyExcel.writerSheet(0, "金融机构贷款方式情况表").build();
		excelWriter.write(voList, writeSheet);
		log.info("写入excel完毕");

		excelWriter.finish();
	}
	
	private void calTarget110(List<Loan> loanList, String clientType, List<Target110Vo> voList) {
		Map<String, List<Loan>> map = new HashMap<>();
		map.put("保证", new ArrayList<>());
		map.put("抵质押", new ArrayList<>());
		map.put("信用", new ArrayList<>());
		BigDecimal totalLoanBalance = new BigDecimal("0");
		for(Loan loan : loanList) {
			List<Loan> list = map.get(loan.getGuaranteeType());
			list.add(loan);
			totalLoanBalance = totalLoanBalance.add(loan.getLoanBalance());
		}
		String[] types = new String[] {"保证", "抵质押", "信用"};
		for(int i = 0; i < types.length; i++) {
			String type = types[i];
			List<Loan> list = map.get(type);
			BigDecimal subLoanBalance = new BigDecimal("0");
			BigDecimal subLoan = new BigDecimal("0");
			BigDecimal prop = new BigDecimal("0");
			BigDecimal rate = new BigDecimal("0");
			for(Loan loanItem : list) {
				subLoanBalance = subLoanBalance.add(loanItem.getLoanBalance());
				subLoan = subLoan.add(loanItem.getLoan());
				rate = rate.add(loanItem.getLoan().multiply(loanItem.getInterestRate()));
			}
			Target110Vo vo = new Target110Vo();
			vo.setClientType(clientType);
			vo.setGuarantee(type);
			vo.setLoan(subLoanBalance.setScale(2, RoundingMode.HALF_UP));
			vo.setProportion(subLoanBalance.divide(totalLoanBalance, RoundingMode.HALF_UP).multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP));
			vo.setRate(rate.divide(subLoan, RoundingMode.HALF_UP).setScale(2, RoundingMode.HALF_UP));
			vo.setCount(list.size());
			voList.add(vo);
		}
		
		
	}
	
	
	
}
